﻿using Nlnet.Avalonia.Controls;
using OfficeOpenXml;
using OfficeOpenXml.FormulaParsing;

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

using 畜牧业审核程序国产.Models.ToolsClass;

namespace 畜牧业审核程序国产.Models.PreReview
{
    internal abstract class Animal
    {
        public int 调查户编码所在行 { get; set; }
        public int 调查户编码所在列 { get; set; }
        public int 开始行 { get; set; }
        public int 结束行 { get; set; }
        public int 填报指标数量 { get; set; }
        public int 村信息所在行 { get; set; }
        public int 村信息所在列 { get; set; }
        public int 时点数结束行 { get; set; }
        public int _seasonNow = Global.IntSeasonNow;
        public int _yearNow = Global.IntYearNow;
        public int 列宽调整右列
        {
            get
            {
                if (_seasonNow == 2)
                {
                    return 21;
                }
                else if (_seasonNow == 3)
                {
                    return 23;
                }
                else //if(_seasonNow ==4)
                { return 25; }
            }
        }//从20列开始

             public string Name { get; set; }
        public string TableName { get; set; }
        public int 核实性错误说明展示列
        {
            get
            {
                if (_seasonNow == 1)
                {
                    return 20;
                }
                else if (_seasonNow == 2)
                {
                    return 22;
                }
                else if (_seasonNow == 3)
                {
                    return 24;
                }
                else
                { return 26; }
            }
        }
        public int 上季度数据所在列
        {

            get
            {
                if (_seasonNow == 1)
                {
                    return 40;
                }
                else if (_seasonNow == 2)
                {
                    return 7;
                }
                else if (_seasonNow == 3)
                {
                    return 11;
                }
                else
                { return 15; }
            }
        }



        public int 核实性错误说明填写列
        {
            get
            {
                if (_seasonNow == 1)
                {
                    return 21;
                }
                else if (_seasonNow == 2)
                {
                    return 23;
                }
                else if (_seasonNow == 3)
                {
                    return 25;
                }
                else
                { return 27; }
            }

        }
        
        public int 本季度数据所在列
        {
            get
            {
                if (_seasonNow == 1)
                {
                    return 7;
                }
                else if (_seasonNow == 2)
                {
                    return 11;
                }
                else if (_seasonNow == 3)
                {
                    return 15;
                }
                else
                { return 19; }

            }

        }
        //public int 锁定列
        //{
        //    get
        //    {
        //        if (季度数 == 2)
        //        {
        //            return 6;
        //        }
        //        else if (季度数 == 3)
        //        {
        //            return 10;
        //        }
        //        else if (季度数 == 4)
        //        {
        //            return 14;
        //        }
        //        else if (季度数 == 1)
        //        {
        //            return 0;

        //        }
        //        else
        //        { return 0; }
        //    }
        //}
        //public string GetTheFarmingCode(ExcelPackage.wo)
        public abstract string MandatoryReview(ExcelWorksheet sht);//月度强制性审核

        public abstract void VerificationReview(ExcelWorksheet sht);//月度核实性审核
        
        //public void 删除所有批注并恢复单元格颜色(ExcelWorksheet sht)
        //{ 
        //var usedRange = sht.Dimension;
        //    if (usedRange == null)
        //    {
        //        return;
        //    }
        //    var ranges = sht.Cells[usedRange.Address];
        //    foreach (var rng in ranges)
        //    {
        //        if (rng.Comment != null)
        //        {
        //            sht.Comments.Remove(rng.Comment);
        //        }
        //    }
        //}
       public string GetFarmCode(ExcelWorksheet sht, int 行, int 列)
        {
            char 冒号;
            string 单元格内容 = Convert.ToString(sht.Cells[行, 列].Value);
            if (Regex.IsMatch(单元格内容, "：") == true)
            {
                冒号 = '：';
            }
            else if (Regex.IsMatch(单元格内容, ":") == true)
            {
                冒号 = ':';
            }
            else
            {
                return ($"[{sht.Name}]户编码填写不符合规范,把冒号漏了");

            }
            int INT_冒号位置 = 单元格内容.IndexOf(冒号);
            string 单元格冒号后内容 = 单元格内容.Substring(INT_冒号位置 + 1).Trim();

            string numberPattern = @"\d+";

            MatchCollection matches = Regex.Matches(单元格冒号后内容, numberPattern);
            if (matches.Count == 0)
            {
                return $"[{sht.Name}]未填写户编码";

            }
            // 将所有匹配到的数字拼接为一个字符串
            string result = "";
            foreach (Match match in matches)
            {
                result += match.Value;
            }
            return result;
        }
        public void 恢复单元格颜色并删除注释(ExcelWorksheet sht)
        {
            var usedRange = sht.Dimension;
            if (usedRange != null)
            {
                //var range = sht.Cells[usedRange.Start.Row, usedRange.Start.Column, usedRange.End.Row, usedRange.End.Column];
                var ranges = sht.Cells[usedRange.Address];//跟上面有啥不一样?
                ranges.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.None;
               // ranges.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.White); 
                foreach (var rng in ranges)
                {
                    if (rng.Comment !=null)
                    {
                        sht.Comments.Remove(rng.Comment);
                        
                    }
                }
            }
        }
        public void 本季度及上季度指标空值转成0(ExcelWorksheet sht)//季度两列
        {
            var startCell = sht.Cells[开始行, 本季度数据所在列];
            var endCell = sht.Cells[开始行 + 填报指标数量 - 1, 本季度数据所在列];
            var ranges = sht.Cells[startCell.Address + ":" + endCell.Address];
            foreach (var rng in ranges)
            {
                if (rng == null || rng.Value == null)
                {
                    rng.Value = 0;
                }
            }
            // 设置水平对齐为居中
            ranges.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            // 设置垂直对齐为居中
            ranges.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
            startCell = sht.Cells[开始行, 上季度数据所在列];
            endCell = sht.Cells[开始行 + 填报指标数量 - 1, 上季度数据所在列];
            ranges = sht.Cells[startCell.Address + ":" + endCell.Address];
            foreach (var rng in ranges)
            {
                if (rng == null || rng.Value == null)
                {
                    rng.Value = 0;
                }
            }
            // 设置水平对齐为居中
            ranges.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            // 设置垂直对齐为居中
            ranges.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
    
        }

        public void 本月及上季末月指标空值转成0并所有单元格全部居中(ExcelWorksheet sht)
        {
            var startCell = sht.Cells[开始行, this.本季度数据所在列 - 5];
            var endCell = sht.Cells[开始行+填报指标数量-1, this.本季度数据所在列 -1];
            //选择区域
            ExcelRange ranges = sht.Cells[startCell.Address + ":" + endCell.Address];
            
            //对选定区域进行操作
            foreach (var rng in ranges)
            {
                if (rng==null || rng.Value ==null)
                {
                    rng.Value = 0;
                }   
            }
            // 设置水平对齐为居中
            ranges.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            // 设置垂直对齐为居中
            ranges.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;



        }
        public void 错误单元格标注成红色并加注释(string 错误信息, ExcelWorksheet sht, params ExcelRange[] ranges)
        {
            foreach (var rng in ranges)
            {
                rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Red);
                if (rng.Comment !=null)
                {
                    string commentExisted = rng.Comment.Text;
                    string newComment = commentExisted + Environment.NewLine +"***"+ 错误信息;
                    sht.Comments.Remove(rng.Comment);
                    rng.AddComment(newComment);
                    rng.Comment.AutoFit = true;
                   
                    //rng.Comment.RichText.Add(newComment);


                }
                else
                {
                    rng.AddComment("***"+错误信息);
                    rng.Comment.AutoFit = true;
                }
               
                

                //// 设置单个单元格的值
                //worksheet.Cells[1, 1].Value = "Header 1";
                //worksheet.Cells[1, 2].Value = "Header 2";

                //// 设置一个单元格范围的值
                //var range = worksheet.Cells["A2:B4"];
                //range.Value = "Sample Range Value";

                //// 设置字体样式
                //range.Style.Font.Name = "Arial";
                //range.Style.Font.Size = 12;
                //range.Style.Font.Bold = true;

                //// 设置背景颜色
                //range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                //range.Style.Fill.BackgroundColor.SetColor(Color.LightGreen);

                //// 设置边框样式
                //range.Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                //range.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                //range.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                //range.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                //range.Style.Border.Top.Color.SetColor(Color.Black);
                //range.Style.Border.Left.Color.SetColor(Color.Black);
                //range.Style.Border.Right.Color.SetColor(Color.Black);
                //range.Style.Border.Bottom.Color.SetColor(Color.Black);

                //// 设置对齐方式
                //range.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                //range.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;

                //// 设置数字格式
                //worksheet.Cells["C1"].Value = 1234.56;
                //worksheet.Cells["C1"].Style.Numberformat.Format = "#,##0.00";

                //worksheet.Cells["C2"].Value = 0.789;
                //worksheet.Cells["C2"].Style.Numberformat.Format = "0.00%";

                //worksheet.Cells["C3"].Value = DateTime.Now;
                //worksheet.Cells["C3"].Style.Numberformat.Format = "yyyy-mm-dd";
            }
            
        }
        


        public Animal()
        {
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        }
        public void 核实性说明标题填写(ExcelWorksheet sht)
        {
            ExcelRange rng核实性错误标题 = sht.Cells[开始行 - 2, this.核实性错误说明展示列];
            rng核实性错误标题.Value = $"第{_seasonNow.ToString()}季度核实性错误情况";
            rng核实性错误标题.Style.Font.Size = 10; rng核实性错误标题.Style.Font.Bold = true;
            
            ExcelRange rng核实性错误描述 = sht.Cells[this.开始行 - 1, this.核实性错误说明展示列];
            rng核实性错误描述.Value = $"第{_seasonNow.ToString()}季度核实性错误内容";
            rng核实性错误描述.Style.Font.Size = 10; rng核实性错误描述.Style.Font.Bold = true;

            ExcelRange rng核实性说明填写 = sht.Cells[this.开始行 - 1, this.核实性错误说明填写列];
            rng核实性说明填写.Value = $"第{_seasonNow.ToString()}季度核实性错误说明";
            rng核实性说明填写.Style.Font.Size = 10; rng核实性说明填写.Style.Font.Bold = true;

            sht.Column(核实性错误说明展示列).Width = 20;
            sht.Column(核实性错误说明填写列).Width = 20;
        }
        public void 核实数据输出至台账( List<string> ListSTR_核实性错误内容, ExcelWorksheet sht)
        {
            if (ListSTR_核实性错误内容.Count > 0)
            {
                int rowCount = sht.Dimension.Rows;
                int columnCount = sht.Dimension.Columns;
                var lastRowCellOf核实性错误说明展示列 = sht.Cells.LastOrDefault(c => c.Start.Column == 核实性错误说明展示列 &&string.IsNullOrWhiteSpace(c.Text)==false).Start.Row+1;
                //通过start.row来获取单元格所在的行数
                if (lastRowCellOf核实性错误说明展示列 == 开始行 )//如果说相等的话,说明用户还没有审核过,此时只需要从第九行开始往下写入错误即可
                {
                    int rowTick = 开始行;
                    foreach (string item in ListSTR_核实性错误内容)
                    {
                        sht.Cells[rowTick, 核实性错误说明展示列].Value = item;
                        rowTick++;
                    }
                }
                else//否则说明用户已经审核过了,
                {
                    //用数组比较的方式来写
                    //先把已经有的错误和说明写到字典里
                    Dictionary<string, string> existedVerification_Dic = new Dictionary<string, string>();
                    for (int i = 开始行; i < lastRowCellOf核实性错误说明展示列; i++)
                    {
                        existedVerification_Dic.Add(sht.Cells[i, 核实性错误说明展示列].Value.ToString(),
                            sht.Cells[i, 核实性错误说明填写列].Value == null ? null : sht.Cells[i, 核实性错误说明填写列].Value.ToString());
                        
                    }
                    //把
                    Dictionary<string, string> newVerification_Dic = new Dictionary<string, string>();
                    foreach (var item in ListSTR_核实性错误内容)
                    {
                        if (existedVerification_Dic.ContainsKey(item) && existedVerification_Dic[item] != null )
                        {
                            newVerification_Dic.Add(item, existedVerification_Dic[item]);
                        }
                        else
                        {
                            newVerification_Dic.Add(item, null);
                        }
                    }

                    //清除台账上的已有内容
                    ExcelRange startCell = sht.Cells[开始行, 核实性错误说明展示列];
                    ExcelRange endCell = sht.Cells[lastRowCellOf核实性错误说明展示列, 核实性错误说明填写列];
                    ExcelRange   ranges = sht.Cells[startCell.Address + ":" + endCell.Address];
                    ranges.Clear();
                    //写入新的内容
                    int j = 开始行;
                    foreach (var item in newVerification_Dic)
                    {
                        sht.Cells[j, 核实性错误说明展示列].Value = item.Key;
                        sht.Cells[j, 核实性错误说明填写列].Value = item.Value;
                        j++;
                    }











                    //for (int i = 开始行; i < lastRowCellOf核实性错误说明展示列; i++)
                    //{
                    //    string 已有单元格信息 = sht.Cells[i,核实性错误说明展示列].Text;
                    //    for (int j = ListSTR_核实性错误内容.Count - 1; j >=0; j--)
                    //    {
                    //        if (ListSTR_核实性错误内容[j].Equals(已有单元格信息))
                    //        {
                    //            ListSTR_核实性错误内容.RemoveAt(j);
                    //        }
                    //    }
                    //}
                    //if (ListSTR_核实性错误内容.Count>0)
                    //{
                    //    int rowTick = lastRowCellOf核实性错误说明展示列;
                    //    foreach (string item in ListSTR_核实性错误内容)
                    //    {
                    //        sht.Cells[rowTick, 核实性错误说明展示列].Value = item;
                    //        rowTick++;
                    //    }
                    //    //接下去把已经修正的问题从展示列和说明列上删去
                    //    string 比对终点 = sht.Cells[lastRowCellOf核实性错误说明展示列, 核实性错误说明展示列].Text;
                    //    //如果下面这种比法会有问题
                    //    for (int i = 开始行; i < lastRowCellOf核实性错误说明展示列; i++)
                    //    {
                    //        string i行展示列单元格信息 = sht.Cells[i, 核实性错误说明展示列].Text;
                    //        if (Regex.IsMatch(i行展示列单元格信息, 错误期别) == false)
                    //        {
                    //            continue;
                    //        }
                    //        bool i行的错误是否还有 = false;
                    //        //foreach (string item in ArrSTR_核实性错误内容)
                    //        //{
                    //        //    if (item.Equals(已有的某条单元格信息))
                    //        //    {
                    //        //        i行的错误是否还有 = true;
                    //        //        //break;
                    //        //    }
                    //        //}

                    //        for (int k = 0; k < ListSTR_核实性错误内容.Count; k++)
                    //        {

                    //            if (ListSTR_核实性错误内容[k].Equals(i行展示列单元格信息))
                    //            {
                    //                i行的错误是否还有 = true;
                    //            }
                    //        }

                    //        if (i行的错误是否还有 == false)
                    //        {
                    //            sht.Cells[i, 核实性错误说明展示列].Value = "可删除";
                    //        }
                    //    }
                    //    #region 删除掉"可删除"
                    //    List<ExcelRangeBase> cellsToDelete = new List<ExcelRangeBase>();
                    //    ExcelRange startCell = sht.Cells[开始行, 核实性错误说明展示列];
                    //    ExcelRange endCell = sht.Cells[lastRowCellOf核实性错误说明展示列+ ListSTR_核实性错误内容.Count, 核实性错误说明展示列];
                    //    ExcelRangeBase ranges = sht.Cells[startCell.Address + ":" + endCell.Address];
                    //    foreach (var item in ranges)
                    //    {
                    //        if ("可删除".Equals(item.Value))
                    //        {
                    //            cellsToDelete.Add(item);
                    //            cellsToDelete.Add(item.Offset(0,1));//把右边的也选上,也可以使用item.column+1
                    //        }
                    //    }

                    //    foreach (ExcelRangeBase cell in cellsToDelete)
                    //    {
                    //        cell.Delete(eShiftTypeDelete.Up);
                    //    }
                    //    #endregion


                    // }
                }
            }
        
        }

        public bool 检查全年填报区域是否有非数值单元格(ExcelWorksheet sht)
        {
            var startCell = sht.Cells[开始行,4];
            var endCell = sht.Cells[开始行 + 填报指标数量 - 1, 19];
            var ranges = sht.Cells[startCell.Address + ":" + endCell.Address];
            bool 是否有非数值单元格 = false;
            foreach (var rng in ranges)
            {
                //string pattern = @"^[+-]?(\d+|\d*\.\d+)$";
                string pattern = @"^\d+(\.\d+)?$";
                if (rng.Value != null && Regex.IsMatch(rng.Value.ToString(), pattern) == false)
                {
                    rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                    rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.BlueViolet);
                    //rng.AddComment("请填写数值");
                    是否有非数值单元格 = true;
                }
            }
            return 是否有非数值单元格;
        }







    }
}
